import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine("mysql+pymysql://{}@{}:3306/{}?".format("root","123456","localhost","person"))
conn = engine.connect()
# 读入管家婆信息
Data = pd.read_excel(f"E:\Program\Python\First\HKRProjects\Data\度小满管家婆.xlsx")
Data.to_sql("DuFinance", engine, if_exists="replace", index=False)  # 上传数据

# 建立用户列表
sql = """
    create table DuUser(
	    username varchar(20),
	    passwd int(6),
	    status int(2)
    )
"""
# status状态，1:正常 0:锁定
conn.execute(text(sql))
conn.execute(text("INSERT INTO DuUser VALUE ('admin', 123456, 1), ('lisi', 123456, 1)"))
conn.commit()

Data = pd.read_sql_query(text("SELECT * FROM DuFinance"), conn)
UserList = pd.read_sql_query(text("SELECT * FROM DuUser"), conn)
UserLoginNum = 0
User = input("请输入要登陆的用户名：")
if User not in UserList['username'].to_list():
    UserLoginNum = -1
elif UserList[UserList['username'] == User]['status'].to_list()[0] == 0:
    print("账户已锁定，请联系系统管理员解锁！")

while 0 <= UserLoginNum < 3 and UserList[UserList['username'] == User]['status'].to_list()[0] == 1:
    Passwd = eval(input("请输入密码（还有 {} 次机会）：".format(3-UserLoginNum)))
    if Passwd == UserList[UserList['username'] == User]['passwd'].to_list()[0]:
        print("登陆成功")
        break
    UserLoginNum += 1
if UserLoginNum >= 3:
    conn.execute(text("UPDATE DuUser SET status = 0 WHERE username = '{}'".format(User)))
    conn.commit()
    print("输入密码错误次数过多，已锁定，请联系系统管理员解锁！")
elif UserLoginNum == -1:
    print("该用户还未注册!!!")
elif UserList[UserList['username'] == User]['status'].to_list()[0] == 1:
    if User != "admin":
        result = eval(input("1.查询所有 2.按条件查询"))
        if result != 1:
            print("该功能还未开发...")
        print(Data)
    else:
        print("1.添加账务 2.编辑账务 3.删除账务 4.查询账务 5.退出系统")
        result = eval(input("请输入要操作的功能序号[1-5]："))
        if result != 3:
            print("该功能还未开发...")
        else:
            result = eval(input("请输入删除账务的ID"))
            conn.execute(text("DELETE FROM DuFinance WHERE ID = {}".format(result)))
            conn.commit()
            print("删除账务成功")